﻿using SMALLERP.DataClass;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace XJLSC.RP
{
    public partial class XSReport : Form
    {
        private SqlDataReader sdr1;
        DataBase db = new DataBase();
        public XSReport()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            String strSql = "SELECT RTRIM(MA001) 客户,RTRIM(MA002) 客户名称,CAST((SELECT ISNULL(SUM(TH008),0) TH008 FROM COPTH LEFT JOIN COPTG ON TH001 = TG001 AND TH002 = TG002 WHERE TG004 = MA001 AND TH020 = 'Y' AND TH004 LIKE '8%' AND TG003>=CONVERT(CHAR(8),DATEADD(DAY,0,'" + dateTimePicker1.Value.ToShortDateString() + "'),112)  AND TG003<=CONVERT(CHAR(8),DATEADD(DAY,0,'" + dateTimePicker2.Value.ToShortDateString() + "'),112)) AS REAL) 销货数,CAST((SELECT ISNULL(SUM(TJ007),0) TJ007 FROM COPTJ LEFT JOIN COPTI ON TI001 = TJ001 AND TI002 = TJ002 WHERE TI004 = MA001 AND TI019 = 'Y' AND TJ004 LIKE '8%' AND TI003>=CONVERT(CHAR(8),DATEADD(DAY,0,'" + dateTimePicker1.Value.ToShortDateString() + "'),112)  AND TI003<=CONVERT(CHAR(8),DATEADD(DAY,0,'" + dateTimePicker2.Value.ToShortDateString() + "'),112)) AS REAL) 退货数,0.00 退货率 FROM COPMA WHERE EXISTS(SELECT * FROM COPTH LEFT JOIN COPTG ON TG001 = TH001 AND TG002 = TH002 WHERE TG004 = MA001 AND TH004 LIKE '8%')";
            try
            {
                dataGridView1.DataSource = db.GetDataSet1(strSql, "PURMA").Tables["PURMA"];
                this.dataGridView1.AlternatingRowsDefaultCellStyle.BackColor = Color.LightGray;
                this.dataGridView1.AllowUserToAddRows = false;
                for (int i = 0; i < dataGridView1.Rows.Count - 1; i++)
                {
                    if (Convert.ToDouble(dataGridView1.Rows[i].Cells["销货数"].Value) > 0)
                    {
                        dataGridView1.Rows[i].Cells["退货率"].Value = (Math.Round(Convert.ToDouble(dataGridView1.Rows[i].Cells["退货数"].Value) / Convert.ToDouble(dataGridView1.Rows[i].Cells["销货数"].Value) * 100, 6)).ToString() + "%";
                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "软件提示");
                //throw ex;
            }
        }

        private void button2_Click(object sender, EventArgs e)
        {
            String strSql = "SELECT RTRIM(MA001) 客户,RTRIM(MA002) 客户名称,CAST((SELECT COUNT(*) SKPC FROM ACRTK LEFT JOIN ACRTL ON TL001=TK001 AND TK002=TL002 WHERE TK004 = MA001 AND TK020 = 'Y' AND TL004='4' AND TK003>=CONVERT(CHAR(8),DATEADD(DAY,0,'" + dateTimePicker3.Value.ToShortDateString() + "'),112)  AND TK003<=CONVERT(CHAR(8),DATEADD(DAY,0,'" + dateTimePicker4.Value.ToShortDateString() + "'),112)) AS REAL) 总回款批次,0 回款周期 FROM COPMA WHERE EXISTS(SELECT * FROM COPTH LEFT JOIN COPTG ON TG001 = TH001 AND TG002 = TH002 WHERE TG004 = MA001 AND TH004 LIKE '8%')";
            try
            {
                dataGridView2.DataSource = db.GetDataSet1(strSql, "PURMA").Tables["PURMA"];
                this.dataGridView2.AlternatingRowsDefaultCellStyle.BackColor = Color.LightGray;
                this.dataGridView2.AllowUserToAddRows = false;
                for (int i = 0; i < dataGridView2.Rows.Count - 1; i++)
                {
                    if (Convert.ToInt16(dataGridView2.Rows[i].Cells["总回款批次"]) > 0)
                    {
                        strSql = "SELECT SUM(DATEDIFF(DAY,TA020,TK003))/" + dataGridView2.Rows[i].Cells["总回款批次"].ToString() + " 回款周期 FROM ACRTK LEFT JOIN ACRTL ON TL001=TK001 AND TK002=TL002 LEFT JOIN ACRTA ON TL005=TA001 AND TL006=TA002 WHERE TL004='1' AND TK020 = 'Y' AND TK004='" + dataGridView2.Rows[i].Cells["客户"].ToString() + "' AND TK003>=CONVERT(CHAR(8),DATEADD(DAY,0,'" + dateTimePicker3.Value.ToShortDateString() + "'),112)  AND TG003<=CONVERT(CHAR(8),DATEADD(DAY,0,'" + dateTimePicker4.Value.ToShortDateString() + "'),112)";
                        try
                        {
                            sdr1 = db.GetDataReader1(strSql);

                            while (sdr1.Read())
                            {
                                dataGridView2.Rows[i].Cells["回款周期"].Value = sdr1["回款周期"].ToString();
                            }
                        }
                        catch (Exception ex)
                        {
                            MessageBox.Show(ex.Message, "软件提示");
                            //throw ex;
                        }
                        finally
                        {
                            sdr1.Close();
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "软件提示");
                //throw ex;
            }
        }

        private void dataGridView1_DataError(object sender, DataGridViewDataErrorEventArgs e)
        {

        }

        private void dataGridView2_DataError(object sender, DataGridViewDataErrorEventArgs e)
        {

        }

        private void dataGridView3_DataError(object sender, DataGridViewDataErrorEventArgs e)
        {

        }
    }
}
